<?php
class Model_baza extends MY_Model {

	function __construct() {
		parent::__construct();
		$this->aUser = $this->usersession->currentUser();
	}

	function bringBaza($aData, $aWhere) {


		if (! isset($aData["export"])) {
			$this->db->select(" contact.* ", FALSE);
		}

		/*if ($aData["client_id"] != "") {
			$this->db->join("(select DISTINCT(person_id),`client_id`  from `client_persons` where `client_persons`.`extern` = 0 AND client_id in (".str_replace("=", ",", $aData["client_id"]).") ) CC", "CC.person_id=persons.person_id", "inner");
		}*/

		if (! empty($aData["limit"])) {
			$this->db->limit($aData["limit"], $aData["start"]);
		}
		if (! empty($aData["sort"])) {
			$this->db->order_by($aData["sort"], $aData["dir"]);
		}

		$query = $this->db->get("contact");
		$result = $query->result_array();
		$totalCount = $this->db->count_all_results("contact");
		$this->db->flush_cache();
		$data = array('error' => FALSE, 'totalCount' => $totalCount, 'data' => $result);

		return $data;
	}



	function getWhere($aData) {

		$filtru = ' 1=1 ';

		if (isset($aData['client_id_not']) && $aData['client_id_not'] != '') {
			$filtru .= " AND persons.person_id not in ( SELECT DISTINCT(person_id) from client_persons where  client_id in ( ".implode(",", explode("=", $aData['client_id_not']))."  )  ) ";
		}

		if (isset($aData['name']) && $aData['name'] != '') {
			$name = $this->db->escape("%".$aData['name']."%");
			$filtru .= " AND persons.lname like {$name} ";
		}

		if (isset($aData['fname']) && $aData['fname'] != '') {
			$name = $this->db->escape("%".$aData['fname']."%");
			$filtru .= " AND persons.fname like {$name} ";
		}
		if (isset($aData['company']) && $aData['company'] != '') {
			$company = $this->db->escape("%".$aData['company']."%");
			$filtru .= " AND persons.company like {$company}   ";
		}

		if (isset($aData['email']) && $aData['email'] != '') {
			$email = $this->db->escape("%".$aData['email']."%");
			$filtru .= " AND (persons.email like {$email}  OR  persons.email2 like {$email} ) ";
		}

		if (isset($aData['address']) && $aData['address'] != '') {
			$address = $this->db->escape("%".$aData['address']."%");
			$filtru .= " AND persons.address like {$address}  ";
		}

		if (isset($aData['turnover_an']) && $aData['turnover_an'] != '') {
			if (isset($aData['turnover1']) && $aData['turnover1'] != '') {
				$iCifraMin = $this->db->escape($aData['turnover1']);
				$filtru .= " AND persons.{$aData['turnover_an']} > $iCifraMin  ";
			}
			if (isset($aData['turnover2']) && $aData['turnover2'] != '') {
				$iCifraMax = $this->db->escape($aData['turnover2']);
				$filtru .= " AND persons.{$aData['turnover_an']} < $iCifraMax  ";
			}
		}

		$columns = array('city_id', "county_id", "domain_id", "domain_sub_id", "position_id", "position_sub_id", "sex", "priority", "income", "age", "division_id", "employees", "available", "unsubscribed");

		foreach ($columns as $val) {
			if (isset($aData[$val]) && $aData[$val] != '') {
				$col = str_replace("=", ",", $aData[$val]);
				$filtru .= " AND ( persons.{$val} in ({$col})  ) ";
			}
		}

		if (isset($aData['letter']) && $aData['letter'] != '') {
			$filtru .= " and(  ";
			$litere = $aData['letter'];
			for ($i = 0; $i < strlen($litere); $i ++) {
				if ($litere[$i] != '-')
					$filtru .= " lower(persons.company) REGEXP  '^".strtolower($litere[$i]).".*' or";
				if ($litere[$i] == '-')
					$filtru .= " lower(persons.company) NOT REGEXP  '^[a-z]+'  or";
			}
			$filtru = substr($filtru, 0, - 2);
			$filtru .= " ) ";
		}

		$createdfrom = isset($aData['createdfrom']) ? $aData['createdfrom'] : "";
		//creat_de_la
		$modfrom = isset($aData['modfrom']) ? $aData['modfrom'] : "";
		//creat_de_la
		$end = isset($aData['end']) ? $aData['end'] : "";
		$user = isset($aData['user']) ? str_replace("=", ",", $aData['user']) : "";

		if (($modfrom != '') && ($createdfrom != '')) {
			if ($end != '') {
				$filtru_pana_la_mod = " and persons.date_modified <= '{$end} 59:59:59' ";
				$filtru_pana_la_creat = " and persons.date_created <= '{$end} 59:59:59' ";
			}
			if ($user != '') {
				$filtru_pana_la_mod_pers = " and persons.modify_by in  ({$user})   ";
				$filtru_pana_la_creat_pers = " and persons.create_by  in  ({$user}) ";
			}
			$filtru .= " and  (( persons.date_modified >= '{$modfrom} 00:00:00'  ".$filtru_pana_la_mod_pers." ".$filtru_pana_la_mod."  ) OR ( persons.date_created >= '{$createdfrom} 00:00:00' ".$filtru_pana_la_creat_pers." ".$filtru_pana_la_creat." ))
         ";
		}

		if (($modfrom != '') && ($createdfrom == '')) {
			if ($end != '')
				$filtru_pana_la_mod = " and persons.date_modified <= '{$end} 59:59:59' ";
			if ($user != '')
				$filtru_pana_la_mod_pers = " and persons.modify_by in  ({$user})   ";

			$filtru .= " and ( persons.date_modified >=  '{$modfrom} 00:00:00'  ".$filtru_pana_la_mod_pers." ".$filtru_pana_la_mod."   ) ";
		}

		if (($createdfrom != '') && ($modfrom == '')) {
			if ($end != '')
				$filtru_pana_la_creat = " and persons.date_created <= '{$end} 59:59:59' ";
			if ($user != '')
				$filtru_pana_la_creat_pers = " and persons.create_by  in  ({$user}) ";

			$filtru .= " and ( persons.date_created >= '{$createdfrom} 00:00:00'   ".$filtru_pana_la_creat_pers." ".$filtru_pana_la_creat." )";
		}

		if (($modfrom == '') && ($createdfrom == ''))
			if ($end != '') {
				$filtru_pana_la_mod = " persons.date_modified <= '{$end} 59:59:59' ";
				$filtru_pana_la_creat = "  persons.date_created <= '{$end} 59:59:59' ";

				if ($user != '') {
					$filtru_pana_la_mod_pers = " and persons.modify_by in  ({$user})   ";
					$filtru_pana_la_creat_pers = " and persons.create_by  in  ({$user}) ";
				}
				$filtru .= " and  ((  ".$filtru_pana_la_mod." ".$filtru_pana_la_mod_pers."  ) OR ( ".$filtru_pana_la_creat." ".$filtru_pana_la_creat_pers."  ))";
			}

		return $filtru;

	}

}
